var express = require('express');
var router = express.Router();

function loginOrShowIndex(req, res){
	if(!req.session.user){                     //到达/index路径首先判断是否已经登录
	    res.redirect('login');  //未登录则重定向到 /login 路径
	}
}

/* GET home page. */
router.get('/', function(req, res, next) {
  loginOrShowIndex(req,res);
  res.render('index', { title: 'Express' });
});

/* GET index and storage and in_storage page. */
router
	.get('/index', function(req, res, next) {
  		loginOrShowIndex(req,res);
      res.render("index");
	})
	.get('/storage', function(req, res, next) {
		res.render("storage", {title: 'Express'});
	})
	.get('/in_storage', function(req, res, next) {
		res.render("in_storage", {title: 'Express'});
	});

router.post('/first', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'jingsai'
  });

  connection.connect();

  connection.query('SELECT m0001.M0001002 AS t0003001,t0003002,m02011.M0202002 AS t0003003,m0101.M0101002 AS t0003004' +
    ',t0003005,m02012.M0202002 AS t0003006,t0003007 ' +
    'FROM (t0003,t0004) ' +
    'LEFT OUTER JOIN m0201 m02011 ON m02011.m0201001 = t0003003 ' +
    'LEFT OUTER JOIN m0201 m02012 ON m02012.m0201001 = t0003006 ' +
    'LEFT OUTER JOIN m0001 ON t0003001 = m0001001 ' +
    'LEFT OUTER JOIN m0101 ON t0003004 = m0101001 ' +
    'WHERE t0003002 = t0004002 ' +
    'ORDER BY t0003006,t0003005,t0003002', function (error, results, fields) {
    if (error) throw error;
    res.json(results);
  });
});

router.post('/selectDetail', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'jingsai'
  });

  connection.connect();

  connection.query('SELECT m0001.M0001002 AS t0004001,t0004002,t0004004,t0004005' +
    ',m02011.M0202002 AS t0004006,t0004007,m0101.M0101002 AS t0003004,t0003007,t0003006 ' +
    'FROM (t0003,t0004) ' +
    'LEFT OUTER JOIN m0101 ON t0003004 = m0101001 ' +
    'LEFT OUTER JOIN m0201 m02011 ON m02011.m0201001 = t0004006 ' +
    'LEFT OUTER JOIN m0001 ON t0004001 = m0001001 ' +
    'WHERE t0004002 = ? AND t0004002 = t0003002', [req.body.param], function (error, results, fields) {
    if (error) throw error;
    res.json(results);
  });
});

router.post('/updateInsert', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'jingsai'
  });

  connection.connect();

  console.log('后台开始！')
  console.log('orderNum= '+ req.body.orderNum)
  console.log('storePlace= '+ req.body.storePlace)
  console.log('timeNowStr1= '+ req.body.timeNowStr1)
  var userModSql = 'UPDATE t0003 SET T0003006 = ? WHERE T0003002 = ?';
  var userModSql_Params = ['0604', req.body.orderNum];
  connection.query(userModSql, userModSql_Params, function (error, results, fields) {
    if (error) throw error;
    console.log('UPDATE t0003结束！')
    var userModSql1 = 'UPDATE t0004 SET T0004007 = ? WHERE T0004002 = ?';
    var userModSql_Params1 = [req.body.storePlace,req.body.orderNum];
    connection.query(userModSql1, userModSql_Params1, function (error, results, fields) {
      if (error) throw error;
      console.log('UPDATE t0004结束！')
      var userAddSql = 'INSERT INTO t0001 (SELECT t0003001,t0003002,?,t0003004,?,?,t0003007 FROM t0003 WHERE t0003002 = ?)';
      var userAddSql_Params = ['0301',req.body.timeNowStr1,'0501',req.body.orderNum];
      connection.query(userAddSql, userAddSql_Params, function (error, results, fields) {
        if (error) throw error;
        console.log('INSERT INTO t0001结束！')
        var userAddSql1 = 'INSERT INTO t0002 (SELECT t0004001,t0004002,t0004003,t0004004,t0004005,t0004006,? FROM t0004 WHERE t0004002 = ?)';
        var userAddSql_Params1 = [req.body.storePlace,req.body.orderNum];
        connection.query(userAddSql1, userAddSql_Params1, function (error, results, fields) {
          if (error) throw error;
          console.log('INSERT INTO t0002结束！')
          res.json(results);
        });
      });
    });
  });
});

router.post('/outboundFirst', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'jingsai'
  });

  connection.connect();

  connection.query('SELECT m0001.M0001002 AS t0001001,t0001002,m02011.M0202002 AS t0001003' +
    ',m0101.M0101002 AS t0001004,t0001005,m02012.M0202002 AS t0001006,t0001007 ' +
    'FROM (t0001,t0002) ' +
    'LEFT OUTER JOIN m0201 m02011 ON m02011.m0201001 = t0001003 ' +
    'LEFT OUTER JOIN m0201 m02012 ON m02012.m0201001 = t0001006 ' +
    'LEFT OUTER JOIN m0001 ON t0001001 = m0001001 ' +
    'LEFT OUTER JOIN m0101 ON t0001004 = m0101001 ' +
    'WHERE t0001002 = t0002002 ORDER BY t0001006,t0001005,t0001002', function (error, results, fields) {
    if (error) throw error;
    res.json(results);
  });
});

router.post('/outselectDetail', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'jingsai'
  });

  connection.connect();

  connection.query('SELECT m0001.M0001002 AS t0002001,t0002002,t0002004,t0002005,m02011.M0202002 AS t0002006' +
    ',t0002007,m0101.M0101002 AS t0001004,t0001007,t0001006 ' +
    'FROM (t0001,t0002) ' +
    'LEFT OUTER JOIN m0101 ON t0001004 = m0101001 ' +
    'LEFT OUTER JOIN m0201 m02011 ON m02011.m0201001 = t0002006 ' +
    'LEFT OUTER JOIN m0001 ON t0002001 = m0001001 ' +
    'WHERE t0002002 = ? AND t0002002 = t0001002', [req.body.param], function (error, results, fields) {
    if (error) throw error;
    res.json(results);
  });
});

router.post('/outupdateInsert', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'jingsai'
  });

  connection.connect();

  var userModSql = 'UPDATE t0001 SET T0001006 = ? WHERE T0001002 = ?';
  var userModSql_Params = ['0504', req.body.orderNum];
  connection.query(userModSql, userModSql_Params, function (error, results, fields) {
    if (error) throw error;
    res.json(results);
  });
});

router.post('/inventory', function(req, res, next) {
  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'jingsai'
  });

  connection.connect();

  connection.query('SELECT m0001002,CASE WHEN t0004006 = ? THEN (m0001003 - IFNULL(t0004005,0)) ELSE m0001003 END AS m0001003' +
    ',CASE WHEN t0004006 = ? THEN (m0001004 - IFNULL(t0004005,0)) ELSE m0001004 END AS m0001004' +
    ',CASE WHEN t0004006 = ? THEN (m0001005 - IFNULL(t0004005,0)) ELSE m0001005 END AS m0001005' +
    ',CASE WHEN t0004006 = ? THEN (m0001006 - IFNULL(t0004005,0)) ELSE m0001006 END AS m0001006' +
    ',m02011.M0202002 AS m0001008,m02012.M0202002 AS m0001009 FROM m0001 ' +
    'LEFT OUTER JOIN m0201 m02011 ON m02011.m0201001 = m0001008 ' +
    'LEFT OUTER JOIN m0201 m02012 ON m02012.m0201001 = m0001009 ' +
    'LEFT OUTER JOIN (t0001,t0002,t0003,t0004) ON t0001002 = t0002002 AND t0003002 = t0004002 ' +
    'AND t0001002 = t0003002 AND t0001006 = ? AND t0003006 = ?', ['0701','0702','0703','0704','0501','0604'], function (error, results, fields) {
    if (error) throw error;
    res.json(results);
  });
});

module.exports = router;
